
import json
import os
# pro_info = {}
# pro_info = {"苹果":{"pro_price":5, "pro_stock":200}}
# with open("test.json","w") as fp:
#       fp.write(json.dumps(pro_info))
# 检查文件是否存在
file_path = "test.json"
if os.path.exists(file_path):
    with open(file_path, "r", encoding="utf-8") as fp:
        pro_info = json.load(fp)
else:
      print("文件不存在，将创建新文件")
      with open(file_path,"w",encoding="utf-8") as fp:
            json.dump({}, fp)  # 创建一个空的JSON对象
      pro_info = {}

# pro_info = {"苹果":{"pro_price":5, "pro_stock":200}}
while 1:
      choice = input("请输入你的选择:")
      if choice == "5":
            break
      if choice == "1":
            pro_name = input("请输入商品的名字:")
            pro_price = input("请输入商品的价格:")
            pro_stock = input("请输入商品的库存:")
            if pro_name in pro_info:
                  print("商品已存在，不能添加该商品")
            else:
                  pro_info[pro_name] = {"pro_price": pro_price, "pro_stock": pro_stock}
      elif choice == "2":
            pro_name = input("请输入商品的名字:")
            if pro_name in pro_info:
                  print(f"{pro_name}:价格:{pro_info[pro_name]['pro_price']},库存:{pro_info[pro_name]['pro_stock']}")
            else:
                  print("商品不存在")
      elif choice == "3":
            pro_name = input("请输入要删除的商品名字：")
            if pro_name in pro_info:
                  del pro_info[pro_name]
                  print("商品删除成功")
            else:
                  print("商品不存在，无法删除")
      elif choice == "4":
            pro_name = input("请输入要修改的商品名字：")
            if pro_name in pro_info:
                  pro_price = input("请输入要修改的商品价格：")
                  pro_stock = input("请输入要修改的商品库存：")
                  pro_info[pro_name] = {"pro_price": pro_price, "pro_stock": pro_stock}
                  print("商品修改成功")
            else:
                  print("商品不存在，无法修改")
      else:
            print("无效输入，请重新输入")

with open("test.json", "w") as fp:
      fp.write(json.dumps(pro_info))

import pymysql

# 创建数据库连接
conn = pymysql.connect(
    host='192.168.140.157',
    user='root',
    password='Sctl123456',
    port=3306,
    database='test2',
    charset='utf8mb4'
)

# 创建游标 => 真正执行sql语句
# 开启隐形的事务 => commit才会提交
cur = conn.cursor()

# 先删除同名表，再创建表
sql = "DROP TABLE IF EXISTS test_file_mysql"
cur.execute(sql)
sql = """
CREATE TABLE test_file_mysql (
    pro_name VARCHAR(20) NOT NULL,
    pro_price INT,
    pro_stock INT,
);
"""
cur.execute(sql)
conn.commit()  # 确保表被创建

# 打开CSV文件并读取数据
with open("test.json", "r", encoding="utf-8") as f:
    datas = json.load(f)
    f.readline()
for product_name, details in datas.items():
    pro_price = details["pro_price"]
    pro_stock = details["pro_stock"]
    sql = "INSERT INTO test_file_mysql (pro_name, pro_price, pro_stock) VALUES ({pro_name},{pro_price},{pro_stock});"
    cur.execute(sql)
conn.commit()

# 查询结果
sql = "SELECT * FROM test_file_mysql;"
cur.execute(sql)
# 取结果

print(3, cur.fetchall())

# 关闭游标和连接
cur.close()
conn.close()
